﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
namespace OracleDAL.ElecBoss
{
    public class TicketManager
    {
        public DataSet getTicketSet(string whereStr)
        {
            DataSet ds = new DataSet();
            string strSql = "select distinct t.ID,to_char(DISPATCHTIME,'yyyy-mm-dd hh24:mi:ss') as DISPATCHTIME,to_char(COMPLETETIME,'yyyy-mm-dd hh24:mi:ss') as COMPLETETIME,t.FAULTCONTENT,t.TICKETTYPE,t.SOLVEPROCESS,t.SOLVEMAN,t.TICKETID,t.TICKETNAME,t.FAULTTYPE, c.codevalue,t.ticketstate,t.clfs,v.name as resourcename,v.id as eid,t.alarmcase,t.equiptype,t.yqcompletetime,t.overtime,t.alarmlevel,to_char(alarmstarttime,'yyyy-mm-dd hh24:mi:ss') as alarmstarttime,t.alarmdiscription  from ticket_info t,code c,V_ALARMOBJECT v where v.id=t. EQUIPNAME and  c.codeid=t.FAULTTYPE and" + whereStr + "";
            strSql += " order by  TICKETSTATE,DISPATCHTIME desc";
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;
        }
        public DataSet getTickeCount()
        {
            string strSql = "select count(*) from ticket_info where ticketstate='已完成' ";
            strSql += "union all select count(*) from ticket_info where  ticketstate='处理中' ";
            strSql += "union all select count(*) from ticket_info where  to_char(DISPATCHTIME,'yyyy_mm_dd')=to_char(sysdate,'yyyy_mm_dd') and ticketstate='已完成' ";
            strSql += "union all select count(*) from ticket_info where  to_char(DISPATCHTIME,'yyyy_mm_dd')=to_char(sysdate,'yyyy_mm_dd') and ticketstate='处理中'";
            return DBUtility.OracleHelper.Query(strSql);
        }
        public DataSet getTickeCount(string date)
        {
            string strSql = "select count(*) from ticket_info where ticketstate='已完成' ";
            strSql += "union all select count(*) from ticket_info where  ticketstate='处理中' ";
            strSql += "union all select count(*) from ticket_info where  to_char(DISPATCHTIME,'yyyy-MM-dd')=to_char(to_date('" + date + "','yyyy-MM-dd'),'yyyy-MM-dd') and ticketstate='已完成' ";
            strSql += "union all select count(*) from ticket_info where  to_char(DISPATCHTIME,'yyyy-MM-dd')=to_char(to_date('" + date + "','yyyy-MM-dd'),'yyyy-MM-dd') and ticketstate='处理中'";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 根据告警ID获取工单信息
        /// </summary>
        /// <param name="ALARMID">告警ID</param>
        /// <returns></returns>
        public DataSet GetTicketInfoByAlaram(string ALARMID) {
            StringBuilder sb = new StringBuilder();
            sb.Append("select DISPATCHTIME,COMPLETETIME,FAULTCONTENT,TICKETTYPE,SOLVEPROCESS,SOLVEMAN,TICKETID,ID,TICKETNAME,FAULTTYPE,");
            sb.Append(" FAULTID,TICKETSTATE,CLFS,EQUIPNAME,ALARMCASE,EQUIPTYPE,YQCOMPLETETIME,OVERTIME,ALARMLEVEL,ALARMSTARTTIME,ALARMDISCRIPTION");
            sb.Append(" from TICKET_INFO ");
            sb.Append(" where FAULTID='" + ALARMID + "'");
            return DBUtility.OracleHelper.Query(sb.ToString());
        }
        public int addTicket(string insertSql)
        {
            int result = 0;
            result = DBUtility.OracleHelper.ExecuteSql(insertSql);
            return result;
        }
        /// <summary>
        /// 获取工单信息
        /// </summary>
        /// <param name="strWhere">查询条件/工单ID</param>
        /// <returns></returns>
        public DataSet GetTicketInfo(string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select DISPATCHTIME,COMPLETETIME,FAULTCONTENT,TICKETTYPE,SOLVEPROCESS,SOLVEMAN,TICKETID,ID,TICKETNAME,FAULTTYPE,");
            sb.Append(" FAULTID,TICKETSTATE,CLFS,EQUIPNAME,ALARMCASE,EQUIPTYPE,YQCOMPLETETIME,OVERTIME,ALARMLEVEL,ALARMSTARTTIME,ALARMDISCRIPTION");
            sb.Append(" from TICKET_INFO ");
            sb.Append(" where TICKETID='" + strWhere + "'");
            return DBUtility.OracleHelper.Query(sb.ToString());
        }
        public int judgeExit(string id)
        {
            string strSql = "select count(*) as count from ticket_info where faultid='" + id + "'";
            int result = 0;
            try
            {
                OracleDataReader dr = DBUtility.OracleHelper.ExecuteReader(strSql);
                while (dr.Read())
                {
                    result = Convert.ToInt32(dr["COUNT"].ToString());
                }
            }
            catch (Exception e)
            {
                string aa = e.Message;
            }

            return result;
        }
        public int deleteTicket(string id)
        {
            string strSql = "delete ticket_info where id='" + id + "'";
            int result = 0;
            try
            {
                result = DBUtility.OracleHelper.ExecuteSql(strSql);
            }
            catch (Exception e)
            {

            }
            return result;
        }
        public string getTicketType(string id)//是告警工单 还是手动故障工单
        {
            string strSql = "select tickettype,FAULTID from ticket_info where id='" + id + "'";
            string type = "";
            try
            {
                OracleDataReader dr = DBUtility.OracleHelper.ExecuteReader(strSql);
                while (dr.Read())
                {
                    type = dr["TICKETTYPE"].ToString().ToString() + "|" + dr["FAULTID"].ToString().ToString();
                }
            }
            catch (Exception e)
            {
                string aa = e.Message;
            }
            return type;
        }
        public DataSet getChildSystem()
        {
            string strSql = "select codeid,codevalue from code where codeid like '1001__' order by sort";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 根据告警原因来获取告警的ALARM_MAP表中的资源ID及告警原因号用来处理相似经验
        /// </summary>
        /// <param name="alarmCase">告警原因</param>
        /// <returns></returns>
        public DataSet getAlarmMapInfo(string alarmCase)
        {
            string strSql = "select ALARMMAPID,RESOURCEID,RESOURCENAME,PRIMALCAUSE,ALARMCAUSE,ALARMLEVEL,ALARMTYPE,VENDOR,BELONGSYSTEM from ALARM_MAP WHERE ALARMCAUSE='" + alarmCase + "' ";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 获得设备类型
        /// </summary>
        /// <returns></returns>
        public string getEquipType(string equipName)
        {
            string equipType = "";
            string strSql = "select distinct resourceid,resourcename from alarm_resource where resourcecnname='" + equipName + "'";
            OracleDataReader dr = DBUtility.OracleHelper.ExecuteReader(strSql.ToString());
            while (dr.Read())
            {
                equipType = dr["resourceid"].ToString();
            }
            return equipType;
        }
        public string getAlarmResult(string alarmID)
        {
            string result = "";
            string strSql = "select count(*) as count from alarm_info where ALARMNUMBER='" + alarmID + "' ";

            OracleDataReader dr = DBUtility.OracleHelper.ExecuteReader(strSql);
            while (dr.Read())
            {
                result = dr["count"].ToString();
            }
            dr.Close();
            dr.Dispose();
            return result;

        }
        public void syncTickFromAlarmHis()
        {
            string strSql = "";
            DBUtility.OracleHelper.ExecuteSql(strSql);
        }



    }
}
